package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//通过这个类，封装博客表的基本操作
public class BokeDao {

    //1.新增一个博客

    public void  add(Boke boke)
    {
        Connection connection=null;
        PreparedStatement statement=null;

        try {
            //1.建立连接
            connection= DbUtil.getConnection();
            //2.构造sql语句
            String sql="insert into boke values(null,?,?,?,?)";
            statement=connection.prepareStatement(sql);
            statement.setString(1, boke.getTitle());
            statement.setString(2, boke.getContent());
//            statement.setString(3,boke.getPostTime());
            statement.setTimestamp(3,boke.getPostTimestamp());
            statement.setInt(4,boke.getUserId());
            //3.执行SQL语句
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(connection,statement,null);
        }
    }

    //2.根据博客编号查询博客
    public Boke select (int bokeId)
    {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            //1.建立连接
            connection= DbUtil.getConnection();
            //2.构造sql语句
            String sql="select * from boke where bokeId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bokeId);
            //3.执行SQL语句
            resultSet=statement.executeQuery();
            //4.遍历结果
            //由于bokeId是自增主键，唯一的，所以要么只有1个，要不就没有
            //所以这里不用while，改为if就可以了
            if(resultSet.next())
            {
                Boke boke=new Boke();
                boke.setBokeId(resultSet.getInt("bokeId"));
                boke.setTitle(resultSet.getString("title"));
                boke.setContent(resultSet.getString("content"));
                boke.setPostTime(resultSet.getTimestamp("postTime"));
                boke.setUserId(resultSet.getInt("userId"));
                return boke;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(connection,statement,resultSet);
        }
        return  null;
    }

    //3.直接查询出所有的博客列表

    public List<Boke> selectALL()
    {
        List<Boke> bokes=new ArrayList<>();
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            //1.建立连接
            connection= DbUtil.getConnection();
            //2.构造sql语句
            String sql="select * from boke order by postTime desc";
            statement=connection.prepareStatement(sql);
            //3.执行SQL语句
            resultSet=statement.executeQuery();
            //4.遍历结果
            //由于bokeId是自增主键，唯一的，所以要么只有1个，要不就没有
            //所以这里不用while，改为if就可以了
            while(resultSet.next())
            {
                Boke boke=new Boke();
                boke.setBokeId(resultSet.getInt("bokeId"));
                boke.setTitle(resultSet.getString("title"));
                String content=resultSet.getString("content");
                if(content.length()>=100)
                {
                    content=content.substring(0,100)+"...";
                }
                boke.setContent(content);
                boke.setPostTime(resultSet.getTimestamp("postTime"));
                boke.setUserId(resultSet.getInt("userId"));
                bokes.add(boke);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(connection,statement,resultSet);
        }
        return bokes;
    }

    //4.删除博客

    public void delete(int bokeId)
    {
        Connection connection=null;
        PreparedStatement statement=null;

        try {
            //1.建立连接
            connection= DbUtil.getConnection();
            //2.构造sql语句
            String sql="delete from boke where bokeId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bokeId);
            //3.执行SQL语句
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(connection,statement,null);
        }
    }

}
